package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.GraduateProjectStatus;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.TreeSet;

public final class GraduateProjectStatusDao {
    private static GraduateProjectStatusDao graduateProjectStatusDao = null;
            //new GraduateProjectStatusDao();
    private static Collection<GraduateProjectStatus> graduateProjectStatuses;

//    static {
//        graduateProjectStatuses = new TreeSet<GraduateProjectStatus>();
//        GraduateProjectStatus graduateProjectStatus = new GraduateProjectStatus(1, "申请", "01", "");
//        graduateProjectStatuses.add(graduateProjectStatus);
//        graduateProjectStatuses.add(new GraduateProjectStatus(2, "通过", "02", ""));
//        graduateProjectStatuses.add(new GraduateProjectStatus(3, "退修", "03", ""));
//    }

    private GraduateProjectStatusDao() {
    }

    public static GraduateProjectStatusDao getInstance() {
        if (graduateProjectStatusDao == null){
            graduateProjectStatusDao = new GraduateProjectStatusDao();
        }
        return graduateProjectStatusDao;
    }

    public int count(Connection connection) throws SQLException {
        return CommonDao.getInstance().count(connection,"GraduateProjectStatus");
    }

    public static Collection<GraduateProjectStatus> findAll(List conditions_json,
                                                            Pagination pagination,
                                                            Connection connection)
            throws SQLException {
        Collection<GraduateProjectStatus> graduateProjectStatuses = new HashSet<GraduateProjectStatus>();
        int totalNum = GraduateProjectStatusDao.getInstance().count(connection);

        StringBuilder sql_select = new StringBuilder("SELECT * FROM GraduateProjectStatus");

        if (conditions_json != null) {
            String whereClause = Condition.toWhereClause(conditions_json);
            sql_select.append(whereClause);
        }

        if (pagination != null) {
            sql_select.append(pagination.toLimitClause(totalNum));
        }

        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();

        while (resultSet_select.next()) {
            //根据id查找结果创建GraduateProjectStatus对象
            GraduateProjectStatus graduateProjectStatus = new GraduateProjectStatus(
                    resultSet_select.getInt("id"),
                    resultSet_select.getString("description"),
                    resultSet_select.getString("no"),
                    resultSet_select.getString("remarks"));
            graduateProjectStatuses.add(graduateProjectStatus);
        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        return graduateProjectStatuses;
    }

    public GraduateProjectStatus find(Integer id,Connection connection)
            throws SQLException {

        String findGraduateProjectStatus_sql = "select * from GraduateProjectStatus " +
                "where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt =connection.prepareStatement(findGraduateProjectStatus_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找学院
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();

        //根据id查找结果创建GraduateProjectStatus对象
        GraduateProjectStatus graduateProjectStatus = new GraduateProjectStatus(
                id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"));
        //关闭
        JdbcHelper.close(pstmt,connection);
        return graduateProjectStatus;
    }

    //修改
    public boolean update(GraduateProjectStatus graduateProjectStatus,
                          Connection connection)
            throws SQLException {

        String updateGraduateProjectStatus_sql = "" +
                "UPDATE GraduateProjectStatus SET " +
                "description = ?,no = ?,remarks = ? " +
                "WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateGraduateProjectStatus_sql);
        //为预编译参数赋值
        pstmt.setString(1,graduateProjectStatus.getDescription());
        pstmt.setString(2,graduateProjectStatus.getNo());
        pstmt.setString(3,graduateProjectStatus.getRemarks());
        pstmt.setInt(4,graduateProjectStatus.getId());

        int totalNum = pstmt.executeUpdate();
        JdbcHelper.close(pstmt,null);
        return totalNum > 0;
    }

    //添加学院
    public boolean add(GraduateProjectStatus graduateProjectStatus,Connection connection)
            throws SQLException {
        String addGraduateProjectStatus_sql = "" +
                "INSERT INTO GraduateProjectStatus" +
                "(no,description,remarks) " +
                "VALUES (?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addGraduateProjectStatus_sql);
        //为预编译参数赋值
        pstmt.setString(1,graduateProjectStatus.getNo());
        pstmt.setString(2,graduateProjectStatus.getDescription());
        pstmt.setString(3,graduateProjectStatus.getRemarks());

        int totalNum = pstmt.executeUpdate();
        JdbcHelper.close(pstmt,null);
        return totalNum > 0;
    }

    //根据GraduateProjectStatus对象删除
    public static boolean delete(GraduateProjectStatus graduateProjectStatus,
                                 Connection connection)
            throws SQLException {
        String deleteGraduateProjectStatus_sql = "delete from GraduateProjectStatus " +
                "where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteGraduateProjectStatus_sql);
        //为预编译参数赋值
        pstmt.setInt(1,graduateProjectStatus.getId());

        int totalNum = pstmt.executeUpdate();
        JdbcHelper.close(pstmt,null);
        return totalNum > 0;
    }
}
